/**
 * $Id: Derby.java 23 2006-09-17 20:02:05Z willi.w $
 */
package at.wwu.tunes2db.export;

import java.io.*;
import java.sql.*;

import org.apache.log4j.Logger;


import at.wwu.tunes2db.types.*;

/**
 * @author willi.w
 * Add info to derby database
 */
public class Derby implements Destination {

    public Connection conn;

    private static final Logger log = Logger.getLogger(Derby.class.getName());

    /* (non-Javadoc)
     * @see at.wwu.tunes2db.export.Destination#setup()
     */
    public void open() throws ClassNotFoundException, SQLException, IOException {
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        conn = DriverManager.getConnection("jdbc:derby:itunes;create=true");
        setupTables();
    }

    private void setupTables() throws SQLException, IOException {
        PreparedStatement checkTable = null;
        ResultSet rs = null;
        int tables = 0;
        String checkTableStr = "select tableid, tablename, tabletype from sys.systables where tablename=? and tabletype='T'";
        String[] required = { "GENRES", "ARTISTS", "ALBUMS", "TRACKS" };
        for (String table : required) {
            checkTable = conn.prepareStatement(checkTableStr);
            checkTable.setString(1, table);
            rs = checkTable.executeQuery();
            tables = 0;
            while (rs.next()) {
                log.info(rs.getString(1) + ", " + rs.getString(2) + ", " + rs.getString(3));
                tables++;
                truncateTable(table);
            }
            if (tables == 0) {
                log.info("Table '" + table + "' does not exist.");
                createTable(table);
            }
        }
    }

    public boolean importTable(String table) throws SQLException {
        CallableStatement importTable = null;
        String importTableStr = "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (?,?,?,?,?,?,?)";
        importTable = conn.prepareCall(importTableStr);
        importTable.setNull(1, Types.VARCHAR); // SCHEMANAME
        importTable.setString(2, table); // TABLENAME
        importTable.setString(3, "sql" + File.separator + "derby" + File.separator + table + ".sql"); // FILENAME
        importTable.setNull(4, Types.CHAR); // COLUMNDELIMITER
        importTable.setNull(5, Types.CHAR); // CHARACTERDELIMITER
        importTable.setNull(6, Types.VARCHAR); // CODESET
        importTable.setInt(7, 1); // REPLACE (true)
        return importTable.execute();
    }

    public boolean createTable(String table) throws IOException, SQLException {
        CallableStatement createTable = null;
        StringBuffer createTableStr = new StringBuffer();
        BufferedReader br = new BufferedReader(new FileReader("sql" + File.separator + "derby" + File.separator + "ddl"
                + File.separator + table + ".sql"));
        while (br.ready()) {
            createTableStr.append(br.readLine());
        }
        if (createTableStr.length() > 0) {
            log.info(createTableStr.toString());
            createTable = conn.prepareCall(createTableStr.toString());
            createTable.execute();
        }
        log.info("Table " + table + " created");
        return true;
    }

    public boolean truncateTable(String table) throws SQLException {
        String truncateTableStr = "delete from " + table;
        PreparedStatement truncateTable = null;
        try {
            truncateTable = conn.prepareStatement(truncateTableStr);
            truncateTable.execute();
        } catch (Exception e) {
            log.fatal("Exception truncating " + table,e);
            throw new SQLException(e.getMessage());
        }
        return true;
    }

    /* (non-Javadoc)
     * @see at.wwu.tunes2db.export.Destination#addTrack(at.wwu.tunes2db.types.Track)
     */
    public boolean addTrack(Track t) throws SQLException {
        //log.info("addTrack("+t+")");
        if (t.isNew()) {
            String insertTrackStr = "insert into tracks (id, name, artist_id, album_id, genre_id, total_time) values (?,?,?,?,?,?)";
            PreparedStatement insertTrack = null;
            try {
                insertTrack = conn.prepareStatement(insertTrackStr);
                insertTrack.setInt(1, t.getItunes_id());
                insertTrack.setString(2, t.getName());
                if (t.getArtist() != null) {
                    insertTrack.setInt(3, t.getArtist().getId());
                } else {
                    insertTrack.setNull(3, Types.INTEGER);
                }
                if (t.getAlbum() != null) {
                    insertTrack.setInt(4, t.getAlbum().getId());
                } else {
                    insertTrack.setNull(4, Types.INTEGER);
                }
                if (t.getGenre() != null) {
                    insertTrack.setInt(5, t.getGenre().getId());
                } else {
                    insertTrack.setNull(5, Types.INTEGER);
                }
                insertTrack.setInt(6, t.getTotal_time());
                insertTrack.execute();
            } catch (Exception e) {
                log.fatal("Exception adding " + t,e);
                throw new SQLException(e.getMessage());
            }
            return true;
        }
        return false;
    }

    /* (non-Javadoc)
     * @see at.wwu.tunes2db.export.Destination#addArtist(at.wwu.tunes2db.types.Artist)
     */
    public boolean addArtist(Artist a) throws SQLException {
        //log.info("addArtist("+a+")");
        if (a.isNew()) {
            String insertArtistStr = "insert into artists (id, name) values (?,?)";
            PreparedStatement insertArtist = null;
            try {
                insertArtist = conn.prepareStatement(insertArtistStr);
                insertArtist.setInt(1, a.getId());
                insertArtist.setString(2, a.getName());
                insertArtist.execute();
            } catch (Exception e) {
                log.fatal("Exception adding " + a,e);
                throw new SQLException(e.getMessage());
            }
            return true;
        }
        return false;
    }

    /* (non-Javadoc)
     * @see at.wwu.tunes2db.export.Destination#addAlbum(at.wwu.tunes2db.types.Album)
     */
    public boolean addAlbum(Album l) throws SQLException {
        //log.info("addAlbum("+l+")");
        if (l.isNew()) {
            String insertAlbumStr = "insert into albums (id, name) values (?,?)";
            PreparedStatement insertAlbum = null;
            try {
                insertAlbum = conn.prepareStatement(insertAlbumStr);
                insertAlbum.setInt(1, l.getId());
                insertAlbum.setString(2, l.getName());
                insertAlbum.execute();
            } catch (Exception e) {
                log.fatal("Exception adding " + l,e);
                throw new SQLException(e.getMessage());
            }
            return true;
        }
        return false;
    }

    /* (non-Javadoc)
     * @see at.wwu.tunes2db.export.Destination#addGenre(at.wwu.tunes2db.types.Genre)
     */
    public boolean addGenre(Genre g) throws SQLException {
        //log.info("addGenre("+g+")");
        if (g.isNew()) {
            String insertGenreStr = "insert into genres (id, name) values (?,?)";
            PreparedStatement insertGenre = null;
            try {
                insertGenre = conn.prepareStatement(insertGenreStr);
                insertGenre.setInt(1, g.getId());
                insertGenre.setString(2, g.getName());
                insertGenre.execute();
            } catch (Exception e) {
                log.fatal("Exception adding " + g,e);
                throw new SQLException(e.getMessage());
            }
            return true;
        }
        return false;
    }

    public void close() {
        // TODO Auto-generated method stub

    }

    public boolean addKind(Kind k) throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

    public boolean addType(Type k) throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

}
